<?php
/**
 * Created by PhpStorm.
 * User: yafex
 * Date: 2020/11/30
 * Time: 13:47
 */
class ctrl_wish_report extends L {

    /**
     * 描述 : 获取库存少于100大于0的数量
     * 作者 : Zoro.Zhu
     */
    public function getProductStock(){
        include L::getHtmlTpl('/html/head.html');
        include L::getHtmlTpl('/html/navHover.html');
        set_time_limit(0);
        $a = model_com::getDxmAccount();
        $account = L::sql("SELECT
            account, cookie 
        FROM
            vs_account_cookie 
        WHERE
            account IN ('" . join("','", $a) . "')
        ORDER BY account ASC");
        echo '<div style="text-align: center;font-size: 30px;padding-top: 33px;padding-bottom: 20px">库存小于100大于0的产品数量统计表</div>';
        echo '<div style="text-align: center;"><table border="2" style="font-size: 25px;margin: auto;width: 60%"><tr><td style="text-align: center;font-size: 25px">店小秘账号</td><td style="text-align: center;font-size: 25px">数量</td></tr>';
        $params['shopId'] = -1;
        $params['state'] = 3;
        $params['productState'] = 1;
        $params['inventoryLift'] = 1;
        $params['inventoryRight'] = 99;
        $params['pageSize'] = 300;
        foreach ($account as $acc) {
            if(in_array($acc['account'], ['zeng店小秘', 'zhangqian', 'huzhilin'])){
                continue;
            }
            $productList = serv_rep_dxm::getProduct($acc['cookie'], $params);
            $qty = count($productList);
            echo "<tr><td style=\"text-align: center;font-size: 25px\">{$acc['account']}</td><td style=\"text-align: center;font-size: 25px\">{$qty}</td></tr>";
        }
        echo '</table></div>';
    }


    /**
     * 描述 : 获取库存少于100大于0的数量
     * 作者 : Zoro.Zhu
     */
    public function getPendingOrder(){
        include L::getHtmlTpl('/html/head.html');
        include L::getHtmlTpl('/html/navHover.html');
        set_time_limit(0);
        $a = model_com::getDxmAccount();
        $account = L::sql("SELECT
            account, cookie 
        FROM
            vs_account_cookie 
        WHERE
            account IN ('" . join("','", $a) . "')
        ORDER BY account ASC");
        echo '<div style="text-align: center;font-size: 30px;padding-top: 33px;padding-bottom: 20px">待审核订单数量统计表</div>';
        echo '<div style="text-align: center;"><table border="2" style="font-size: 25px;margin: auto;width: 60%"><tr><td style="text-align: center;font-size: 25px">店小秘账号</td><td style="text-align: center;font-size: 25px">数量</td></tr>';
        foreach ($account as $acc) {
            $orderList = serv_rep_dxm::getPendingOrder($acc['cookie']);
            $qty = count($orderList);
            echo "<tr><td style=\"text-align: center;font-size: 25px\">{$acc['account']}</td><td style=\"text-align: center;font-size: 25px\">{$qty}</td></tr>";
        }
        echo '</table></div>';
    }

    /**
     * 描述 : 订单数量报表
     * 作者 : Zoro.Zhu
     */
    public function orderQty(){
        $this->view->headTitle = '订单数量统计表';
        $this->display('/html/wish/orderQtyReport.html');
    }

    /**
     * 描述 : 获取图标数据
     * 作者 : Zoro.Zhu
     */
    public function getOrderQty(){
        $dxmAccount = model_com::getDxmAccount();
        $date = date('Y-m-d', time() - 86400 * 30);
        $order = "SELECT
            dxmAccount,
            substring( pullTime, 1, 10 ) as time,
            count( 0 ) as qty
        FROM
            `dxm_order` 
        WHERE
            pullTime >= '{$date}' 
        AND dxmAccount IN ('" . join("','", $dxmAccount) . "')
        GROUP BY
            dxmAccount,
            substring( pullTime, 1, 10 )
        order by time";
        $order = L::sql($order);
        $data = [];
        foreach($order as $row){
            $data['account'][$row['dxmAccount']][$row['time']] = $row['qty'];
            $data['time'][$row['time']][$row['dxmAccount']] = $row['qty'];
        }
        $account = array_keys($data['account']);
        $time = array_keys($data['time']);
        $info = [];
        foreach($account as $k => $a){
            $info[$k]['name'] = $a;
            $info[$k]['type'] = 'line';
            foreach($time as $t){
                $info[$k]['data'][] = !empty($data['account'][$a][$t]) ? $data['account'][$a][$t] : 0;
            }
        }
        return ['state' => 200, 'series' => $info, 'legend' => $account, 'xAxis' => $time];
    }

    /**
     * 描述 : 各个国家订单数量统计表
     * 作者 : Zoro.Zhu
     */
    public function countryQty(){
        include L::getHtmlTpl('/html/head.html');
        include L::getHtmlTpl('/html/navHover.html');
        set_time_limit(0);
        $date = date('Y-m', time() - 86400 * 365) . '-01';
        $where = "";
        if(!empty($_GET['sku'])){
            $where .= " and `orderData` LIKE '%{$_GET['sku']}%' ";
        }
        $sql = "SELECT
            chineseName, paymentTime, sum( qty ) AS totalQty 
        FROM
            (
            SELECT
                orderData ->> '$.country' AS chineseName,
                substring( `orderData` ->> '$.paymentTime', 1, 7 ) AS paymentTime,
                count( 0 ) AS qty 
            FROM
                `dxm_order` 
            WHERE
                orderData ->> '$.country' != '中国' 
                AND `orderData` ->> '$.paymentTime' >= '{$date}' 
                {$where}
            GROUP BY
                orderData ->> '$.country',
                substring( `orderData` ->> '$.paymentTime', 1, 7 ) UNION ALL
            SELECT
                b.chineseName, a.paymentTime, a.qty 
            FROM
                (
                SELECT
                    substring( orderData ->> '$.contact',- 2 ) AS contact,
                    substring( `orderData` ->> '$.paymentTime', 1, 7 ) AS paymentTime,
                    count( 0 ) AS qty 
                FROM
                    `dxm_order` 
                WHERE
                    orderData ->> '$.country' = '中国' 
                    AND `orderData` ->> '$.paymentTime' >= '{$date}' 
                    {$where}
                GROUP BY
                    substring( orderData ->> '$.contact',- 2 ),
                    substring( `orderData` ->> '$.paymentTime', 1, 7 ) 
                ) a
                LEFT JOIN country_code b ON a.contact = b.countryCode 
            ) aa 
        WHERE
            paymentTime != 'null' 
        GROUP BY
            chineseName, paymentTime 
        ORDER BY
            paymentTime DESC, totalQty DESC";
        $data = L::sql($sql);
        $info = $y = [];
        foreach($data as $row){
            $y[$row['paymentTime']] = $row['paymentTime'];
            $info[$row['chineseName']][$row['paymentTime']] = $row['totalQty'];
        }
        $onclick = "var sku = $('#sku').val();
        if(sku === ''){
            layer.alert('产品ID为空');
            return false;
        }
        window.location.href = ROOT_URL + '/?c=ctrl_wish_report&a=countryQty&sku=' + sku;";
        echo '<div style="text-align: center;font-size: 30px;padding-top: 33px;padding-bottom: 20px">各个国家订单数量</div>';
        echo '<div style="width: 100%;text-align: center;"><input style="background-color: #FFF;
    background-image: none;
    border: 1px solid #e5e6e7;
    border-radius: 1px;
    color: inherit;
    padding: 6px 12px;
    transition: border-color .15s ease-in-out 0s, box-shadow .15s ease-in-out 0s;
    font-size: 14px;" placeholder="SKU或产品ID" id="sku" value="' . (!empty($_GET['sku']) ? $_GET['sku'] : '') . '" /><input class="btn btn-success" type="button" onclick="' . $onclick . '" value="查询" /></div>';
        echo '<div style="text-align: center;"><table border="2" style="font-size: 20px;margin: auto;width: 90%"><tr><td style="text-align: center;font-size: 20px">国家</td>';
        foreach($y as $v){
            echo "<td style=\"text-align: center;font-size: 20px\">{$v}</td>";
        }
        echo '<tr>';
        foreach($info as $country => $row){
            echo "<tr><td style=\"text-align: center;font-size: 20px\">{$country}</td>";
            foreach($y as $v){
                $qty = !empty($row[$v]) ? $row[$v] : 0;
                echo "<td style=\"text-align: center;font-size: 20px\">{$qty}</td>";
            }
            echo '<tr>';
        }
        echo '</table></div>';
    }

    /**
     * 描述 : 统计店铺余额情况
     * 作者 : Zoro.Zhu
     */
    public function balanceWish(){
        include L::getHtmlTpl('/html/head.html');
        include L::getHtmlTpl('/html/navHover.html');
        set_time_limit(0);
        $account = model_com::getAccount('wish');
        $accountData = L::sql("SELECT
            cookie, xToken, state, merchantId, `name`
        FROM
            `wish_seller_account` 
        WHERE
            `name` in ('" . join("','", $account) . "')");
        echo '<div style="text-align: center;font-size: 30px;padding-top: 33px;padding-bottom: 20px">统计店铺账户余额</div>';
        echo '<div style="text-align: center;"><table border="2" style="font-size: 25px;margin: auto;width: 60%"><tr><td style="text-align: center;font-size: 25px">wish店铺名称</td><td style="text-align: center;font-size: 25px">当前余额</td><td style="text-align: center;font-size: 25px">待处理余额</td></tr>';
        $total = 0;
        foreach($accountData as $row){
            $params = [
                'cookie' => $row['cookie'],
                'xToken' => $row['xToken'],
            ];
            $res = serv_rep_wish::getBalance($params);
            if(isset($res['CONFIRMED']) && isset($res['PENDING'])){
                $res['CONFIRMED'] = round($res['CONFIRMED'], 2);
                $res['PENDING'] = round($res['PENDING'], 2);
                $total += $res['CONFIRMED'];
                $total += $res['PENDING'];
                echo "<tr><td style=\"text-align: center;font-size: 25px\">{$row['name']}</td><td style=\"text-align: center;font-size: 25px\">{$res['CONFIRMED']}</td><td style=\"text-align: center;font-size: 25px\">{$res['PENDING']}</td></tr>";
            }else{
                echo "<tr><td style=\"text-align: center;font-size: 25px\">{$row['name']}</td><td style=\"text-align: center;font-size: 25px\">Cookie失效</td><td style=\"text-align: center;font-size: 25px\">请重新授权</td></tr>";
            }
        }
        echo "<tr><td style=\"text-align: center;font-size: 25px\">合计</td><td style=\"text-align: center;font-size: 25px\">（所有的加起来）</td><td style=\"text-align: center;font-size: 25px;color: red\">{$total}</td></tr>";
        echo '</table></div>';
    }

}

return true;